import java.util.Random;
import java.util.Scanner;
import java.sql.*;

public class admin {
	
	static String id, password;
	static String name, address, registration_num;
	
	
	String url = "jdbc:mysql://localhost:3306/bank";
	String dbid = "root";
	String pw = "1234";
	
	public admin() {
		
		int menu;
		
		do {
			printmenu();
			menu = selectMenu();
			
			switch(menu) {
			case 1:
				accountCreate();
				break;
			case 2:
				accountDelete();
				break;
			case 3:
				AllaccountInfo();
				break;
			case 4:
				break;
			}
		}while(menu!=4);
	}
	

	private void printmenu() {
		// TODO Auto-generated method stub
		System.out.println("1. account 생성");
		System.out.println("2. account 삭제");
		System.out.println("3. 모든 account 정보 보기");
		System.out.println("4. 로그아웃");
		
	}
	
	private int selectMenu() {
		int menuNum;
		Scanner scan = new Scanner(System.in);
		
		while(true) {
			System.out.print("원하시는 메뉴를 선택하세요 >> ");
			menuNum = scan.nextInt();
		
			if(menuNum < 1 || menuNum > 6)
				System.out.println("잘못 입력된 값입니다. 다시 입력 해 주세요.");
			else break;
		}
		return menuNum;
	}
	

	private String getRandomNum() {
		String Num = "";
		int start = 0, end = 9;
		double range = end - start + 1;
	    Random randomGenerator = new Random();
		for(int i = 0; i < 10; i++) {
		    int random = (int)(randomGenerator.nextDouble() * range);
		    Num += random;
		}
		return Num;
		
	}

	private void accountCreate() {
		// TODO Auto-generated method stub
		
		Scanner scan = new Scanner(System.in);
		System.out.print("새로운 account를 생성합니다.\nID를 입력하세요>> ");
		id = scan.next();
		//id중복 확인 필요
		if(existID(id)) {
			do {
				System.out.println("입력한 ID는 이미 사용 중입니다. 다른 ID를 입력하세요.");
				System.out.print("ID를 입력하세요 >> ");
				id = scan.next();
			}while(existID(id));
		}
		System.out.print("password를 입력하세요>> ");
		password = scan.next();
		System.out.print("name을 입력하세요>> ");
		name = scan.next();
		System.out.print("resident registration number를 입력하세요>> ");
		registration_num = scan.next();
		//registration number 중복 확인 필요
		if(existRegistrationNumber(registration_num)) {
			System.out.println("이 resident registration number는 이미 존재하기 때문에 account를 개설 할 수  없습니다.");
			System.out.println("account 생성을 취소합니다.");
		}
		else {
			System.out.print("address를 입력하세요>> ");
			address = scan.next();
			
			Connection conn = null;
			Statement stmt = null;
			PreparedStatement stat;
			
		    String random = getRandomNum();
		    //account number가 중복되지 않는지 check
		    if(existAccountNumber(random)) {
		    	do{
		    		random = getRandomNum();
		    	} while(existAccountNumber(random));
		    }
			
			try {
				Class.forName("com.mysql.jdbc.Driver");
				conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bank", "root", "1234");
				
				if(conn == null)
					throw new Exception("데이터베이스 연결할 수 없음");
				stmt = conn.createStatement();
				
				String command = String.format("insert into customer (id, pw, name, account_num, balance, address, registration_num)" 
						+ "values ('%s', '%s', '%s', '%s', 0, '%s', '%s' )", id, password, name, random, address, registration_num);
				int rowNum = stmt.executeUpdate(command);
				
				if(rowNum < 1)
					throw new Exception("데이터를 DB에 입력할 수 없습니다.");
				
				System.out.println("account가 성공적으로 개설되었습니다.");
				
				System.out.println("ID : " + id);
				System.out.println("passowrd : " + password);
				System.out.println("name : " + name);
				System.out.println("resident registration number : " + registration_num);
				System.out.println("address : " + address);
				System.out.println("balance : 0" );
				System.out.println("account number : " + random );
				
				
			}catch(Exception e) {
				System.out.println(e);
			}
			finally{
				try{
					stmt.close();
				}catch(Exception ignored) {
					
				}
				try{
					conn.close();
				}catch(Exception ignored) {
					
				}
			}
		}
	}
	
	private boolean existID(String id) {
		try{
			Class.forName("com.mysql.jdbc.Driver");
		}catch(ClassNotFoundException e){
			System.out.println("드라이버 연결 실패 : " + e.toString());
		}
	
		try{
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bank", "root", "1234");
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery("SELECT id FROM customer");
			while(rs.next()) {
				if(id.equals(rs.getString(1))) {
					st.close();
					conn.close();
					return true;
				}
			}
			st.close();
			conn.close();
		}catch(SQLException e){
			System.out.println("DB연결 및 작업처리 실패 : " + e.toString());
		}
		return false;
	}
	
	private boolean existRegistrationNumber(String registrationNumber) {
		try{
			Class.forName("com.mysql.jdbc.Driver");
		}catch(ClassNotFoundException e){
			System.out.println("드라이버 연결 실패 : " + e.toString());
		}
	
		try{
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bank", "root", "1234");
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery("SELECT registration_num FROM customer");
			while(rs.next()) {
				if(registrationNumber.equals(rs.getString(1))) {
					st.close();
					conn.close();
					return true;
				}
			}
			st.close();
			conn.close();
		}catch(SQLException e){
			System.out.println("DB연결 및 작업처리 실패 : " + e.toString());
		}
		return false;
	}
	
	private boolean existAccountNumber(String accountNumber) {
		try{
			Class.forName("com.mysql.jdbc.Driver");
		}catch(ClassNotFoundException e){
			System.out.println("드라이버 연결 실패 : " + e.toString());
		}
	
		try{
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bank", "root", "1234");
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery("SELECT account_num FROM customer");
			while(rs.next()) {
				if(accountNumber.equals(rs.getString(1))) {
					st.close();
					conn.close();
					return true;
				}
			}
			st.close();
			conn.close();
		}catch(SQLException e){
			System.out.println("DB연결 및 작업처리 실패 : " + e.toString());
		}
		return false;
	}
	
	private static void accountDelete() {
		// TODO Auto-generated method stub
		Scanner scan2 = new Scanner(System.in);   
		System.out.print("삭제를 원하는 id를 입력하세요 >>");  
		String deleteId = scan2.next();              
		Connection conn = null;        
		Statement stmt = null;           
		PreparedStatement stat;            
		
		try {                     
			Class.forName("com.mysql.jdbc.Driver");   
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bank", "root", "1234");  
			if(conn == null)                            
				throw new Exception("데이터베이스 연결할 수 없음");   
			
			stmt = conn.createStatement();     
			ResultSet rs = stmt.executeQuery("select * from customer where id = '" + deleteId + "';");   
			System.out.println("입력한 id의 정보입니다. 삭제하시겠습니까? y/n");                   
			
			if(rs.next() == true) {              
				System.out.println("ID : " + rs.getString("id"));   
				System.out.println("passowrd : " + rs.getString("pw"));    
				System.out.println("name : " + rs.getString("name"));         
				System.out.println("resident registration number : " + rs.getString("registration_num")); 
				System.out.println("address : " + rs.getString("address"));  
				System.out.println("balance : " + rs.getInt("balance"));           
				System.out.println("account number : " + rs.getString("account_num"));   
				}                                             
			String answer = scan2.next();                    
			if(answer.equals("y"))                     
			{                            
				String command = String.format("delete from customer where id = '"+ deleteId +"';");   
				int rowNum = stmt.executeUpdate(command);     
				if(rowNum < 1)                         
					throw new Exception("데이터를 DB에 입력할 수 없습니다.");     
				System.out.println("정상적으로 삭제가 되었습니다.");      
				}               
			else if(answer.equals("n"))   
			{                          
				System.out.println("삭제가 취소되었습니다.");  
				}                      
			}catch(Exception e) {     
				System.out.println(e);     
				}           
		finally{            
			try{              
				stmt.close();      
				}catch(Exception ignored) {   
					
				}   
			try{                            
				conn.close();                
				}catch(Exception ignored) {      
					
				}   
			}    
	}
	
	private static void AllaccountInfo() {

		// TODO Auto-generated method stub
		String account_num = "";
		int balance = 0;
		
		System.out.println("모든 account 정보 보기");
		
		try{
			Class.forName("com.mysql.jdbc.Driver");
		}catch(ClassNotFoundException e){
			System.out.println("드라이버 연결 실패 : " + e.toString());
		}
	
		try{
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bank", "root", "1234");
			PreparedStatement pstmt = conn.prepareStatement("SELECT account_num, balance FROM customer WHERE id != 'admin';");
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()) {
				account_num = rs.getString(1);
				balance = rs.getInt(2);
				System.out.println("account number : " + account_num + "\tbalance : " + balance);
			}
			pstmt.close();
			conn.close();
		}catch(SQLException e){
			System.out.println("DB연결 및 작업처리 실패 : " + e.toString());
		}
	}
}
